Target Server Support for Macros

You can use ERwin macros in the templates you create for triggers, stored procedures, and pre- and post- schema generation scripts when these features are supported by the target DBMS.

All macros, except those specifically noted, can be used on all of the target servers supported by ERwin. See your server documentation for detailed information on its support for RI triggers, stored procedures, and/or scripts.


Using the Macro Reference

The macro reference information is organized alphabetically by macro name. The information for each macro includes a description, macro syntax, the scope of the macro (i.e., the statements or code segments in which it can be used), the return value; and, typically, an example of the macro template code and corresponding expanded code. A description of each element follows:

♦	Macro Syntax. Provides the syntax for each ERwin macro, including keywords, user-specified variables, and required punctuation.

♦	Scope. For each macro, this element lists:

•	The "%ForEach" commands in which a given macro can be inserted.

•	Whether the macro can be used as the predicate in a conditional "%If" statement.

•	The template types in which a given macro can be inserted. Entries include: RI or Rel Override, Trigger Override, and Global. The specific templates of each type are listed in the following table.

Scope	Templates
RI or Rel Override	PARENT INSERT RESTRICT PARENT INSERT CASCADE PARENT INSERT SET NULL PARENT INSERT SET DEFAULT  PARENT UPDATE RESTRICT PARENT UPDATE CASCADE PARENT UPDATE SET NULL PARENT UPDATE SET DEFAULT PARENT DELETE RESTRICT PARENT DELETE CASCADE PARENT DELETE SET NULL PARENT DELETE SET DEFAULT	CHILD INSERT RESTRICT CHILD INSERT CASCADE CHILD INSERT SET NULL CHILD INSERT SET DEFAULT CHILD UPDATE RESTRICT CHILD UPDATE CASCADE CHILD UPDATE SET NULL CHILD UPDATE SET DEFAULT CHILD DELETE RESTRICT CHILD DELETE CASCADE CHILD DELETE SET NULL CHILD DELETE SET DEFAULT
	Stored procedures attached to tables (macros used within %ForEachFKAtt); pre- and post-scripts (macros used within %ForEachEntity).
Trigger Override	TRIGGER UPDATE HEADER TRIGGER DELETE HEADER TRIGGER INSERT HEADER TRIGGER UPDATE FOOTER TRIGGER DELETE FOOTER	TRIGGER INSERT FOOTER CUSTOM TRIGGER HEADER CUSTOM TRIGGER FOOTER CUSTOM TRIGGER DEFAULT BODY
	Stored procedures attached to tables (macros used within %ForEachAtt); pre- and post-scripts (macros used within %ForEachEntity).
Global	All triggers, stored procedures, and pre- and post-scripts.
♦	Return Value. Explains what SQL code the macro returns when it is generated.

♦	Example. Includes sample template syntax and the corresponding expanded code. All expanded code examples are based on a subset of the tables in the ERwin MOVIES model (SQL Server target) shown in the ERwin Template Toolbox. An example is not included if the macro returns TRUE or FALSE.

Note:	The sample model above shows the physical names for both tables and columns, indicated by the underscore character in the object names (e.g., MOVIE_COPY, movie_copy_number). Different ERwin macros are available to return either the ERwin logical name (which can contain hyphens or spaces) or database physical names (in which hyphens and spaces are replaced by underscores) depending on your requirements.


Reading ERwin Macro Syntax

The ERwin macro syntax commands are composed of keywords, punctuation, and variables.

♦	Keywords always start with a percent sign (%) and must be entered exactly as shown in the table (e.g. %AttFieldName).

♦	Braces {} and parentheses () in the syntax must be included; commas are required if you include more than one variable in the command.

♦	Variables are shown in angle brackets (e.g., <macro code1>). You replace variables with your own specific text, number, or macro code when you type the macro command into a trigger, stored procedure, or script.


Macro Variables

The following list defines common variables used in the macro syntax:

♦	<default value>, <value>, and <initial value> represent a user-specified text or numeric value, which appears exactly as written when the macro code is expanded.

♦	< action> represents a valid SQL action (i.e., INSERT, UPDATE, or DELETE).

♦	<macro code>, <macro code1>...<macro coden> represents any macro code sequence that is valid in the current scope. When multiple macro code sequences can be included in the same command, they are numbered from 1 to n.

♦	<predicate> represents any valid Boolean expression that evaluates to TRUE or FALSE, including those supported directly by an ERwin macro (e.g., %AttIsFK) and those created using supported Boolean operators (e.g., %!= and other comparison operators, %And, %Or, and %Not).

♦	<prefix>, <old prefix>, <new prefix> represents a text or numeric value (e.g., @ins) or expanded macro (e.g., %Parent) that is added to the beginning of a table or column name when the macro code is expanded. If both old and new prefix elements are specified, the list created by the macro has two sections: The first section declares <old prefix> <element>, the second section declares <new prefix> <element>.

♦	<separator> represents one or more characters used to delimit a list created by the macro. Separators are set off by quotation marks (e.g., "OR ").

♦	<table name>, <default name>, <domain name>, <validation> are replaced by the name of an ERwin table, default, domain, or validation rule or the corresponding macro (e.g., %TableName, %Child, %DefaultName, %DomainName, %ValidationName).

♦	<variable> represents the name of a variable used within the trigger, stored procedure, or script.


%!=

A Boolean predicate that returns "true" if  the expansion of <macro code1> is not equal to the expansion of <macro code2>.

%!=(<macro code1>, <macro code2>)

Scope

Global, %If

Return Value

TRUE/FALSE

Example

Template Code	Expanded Code
%ForEachAtt() {%If(%!=(%AttFK) {%AttName}}	general-condition


%%

Inserts a single percent (%) symbol in expanded trigger code.

%%

Scope

Global

Return Value

%

Example

Template Code	Expanded Code
/* %%JoinFKPK	/*%JoinFKPK


%*

Multiplies the expansions of <macro code1> and <macro code2>.

%*(<macro code1>,<macro code2>)

Scope

Global

Return Value

Numeric

Example

Template Code	Expanded Code
string1=”aaaaa” string2=”bbbbbbbbb” %*(%Len(%:string1), %Len(%:string2))	45


%+

Adds the expansions of <macro code1> and <macro code2>.

%+(<macro code1>,<macro code2>)

Scope

Global

Return Value

Numeric

Example

Template Code	Expanded Code
string1=”aaaaa” string2=”bbbbbbbbb” %*(%Len(%:string1), %Len(%:string2))	14


%-

Subtracts the expansions of <macro code2> from <macro code1>.

%-(<macro code1>,<macro code2>)

Scope

Global

Return Value

Numeric

Example

Template Code	Expanded Code
string1=”aaaaa” string2=”bbbbbbbbb” %*(%Len(%:string1), %Len(%:string2))	-4


%/

Divides the expansions of <macro code1> by <macro code2>.

%/(<macro code1>,<macro code2>)

Scope

Global

Return Value

Numeric

Example

Template Code	Expanded Code
string1=”aaaaa” string2=”bbbbbbbbb” %*(%Len(%:string1), %Len(%:string2))	.5556


%:

Returns the value of <variable>.

%:<variable>

Scope

Global

Return Value

Value of the variable <variable>.

Example

Template Code	Expanded Code
%:@ins_customer_id	1213


%<

A Boolean predicate that returns "true" if  the expansion of <macro code1> is less than the expansion of <macro code2>.

%<(<macro code1>, <macro code2>)

Scope

Global, %If

Return Value

TRUE/FALSE


%<=

A Boolean predicate that returns "true" if  the expansion of <macro code1> is less than or equal to the expansion of <macro code2>.

%<=(<macro code1>, <macro code2>)

Scope

Global, %If

Return Value

TRUE/FALSE


%=

Assigns the expansion of <macro code> to  a declared <variable> (See %Decl ). The result of the statement  is variable assignment only, the result does not appear in the expanded code.

%=(<variable>,<macro code>)

Scope

Global

Return Value

None

Example

Template Code	Expanded Code
%=(@var1,%Tablename)	Internally, @var1 is set to the value of %Tablename


%==

A Boolean predicate that returns "true" if  the expansion of <macro code1> is equal to the expansion of <macro code2>.

%==(<macro code1>, <macro code2>)

Scope

Global, %If

Return Value

TRUE/FALSE


%>

A Boolean predicate that returns "true" if  the expansion of <macro code1> is greater than the expansion of <macro code2>

%>(<macro code1>, <macro code2>)

Scope

Global, %If

Return Value

TRUE/FALSE


%>=

A Boolean predicate that returns "true" if  the expansion of <macro code1> is greater than or equal to the expansion of <macro code2>

%>=(<macro code1>, <macro code2>)

Scope

Global, %If

Return Value

TRUE/FALSE


%Action

Returns the name of the action that caused the trigger to fire. Depending on the context of the command, %Action will return DELETE, INSERT, or UPDATE. For example, if the trigger was fired on a delete action, %Action returns DELETE.

%Action

Scope

Trigger Override

Return Value

DELETE, INSERT, or UPDATE

Example

Template Code	Expanded Code
%Action	DELETE


%Actions

Returns a list of actions that were implemented either before or after the trigger fired. If the trigger is a multiple action trigger, the macro reports all actions.

%Actions(<separator>)

Scope

Trigger Override

Return Value

DELETE, INSERT, and/or UPDATE

Example

Template Code	Expanded Code
%Actions(",")	DELETE,DELETE


%And

Lets you combine Boolean expressions to create more complex comparisons. %And performs a "logical and" on Boolean predicates defined by <macro code1> and <macro code2>. For example, the code %If {%And(%AttIsFK, %AttIsRolenamed)…} lets you execute additional steps if the given attribute is both a foreign key and has a rolename. Both elements must evaluate to "TRUE" for the %And condition to be satisfied.

%And(<macro code1>, <macro code2>)

Scope

Global, %If

Return Value

TRUE/FALSE


%AttDatatype

Returns the datatype of the current attribute. Note: Attributes can be assigned a user-defined datatype using ERwin domains. If an attribute has a user-defined datatype, the name of the datatype is provided by this macro.

%AttDatatype

Scope

%ForEachAtt, %ForEachFKAtt

Return Value

Attribute datatype

Example

Template Code	Expanded Code
%ForEachAtt() {%AttName -> %AttDatatype}	master-number -> int movie-copy-number -> int general-condition -> varchar(10)


%AttDef

Returns the attribute definition

%AttDef

Scope

%ForEachAtt, %ForEachFKAtt

Return Value

Attribute definition.

Example

Template Code	Expanded Code
/*If the current att. is master_number, then */ %AttDef	The unique id of a movie.


%AttDefault

Returns the name of the default attached to the attribute.

%AttDefault

Scope

%ForEachAtt, %ForEachFKAtt

Return Value

Name of the default attached to the attribute.

Example

Template Code	Expanded Code
%AttDefault	SPACES


%AttDomain

Returns the name of the domain attached to the attribute.

%AttDomain

Scope

%ForEachAtt, %ForEachFKAtt

Return Value

Name of the domain.

Example

Template Code	Expanded Code
/*If the current att. is cust_phone, then */ %AttDomain	PHONE NUMBER


%AttFieldName

Returns the column name associated with the current attribute.

%AttFieldName

Scope

%ForEachAtt, %ForEachFKAtt

Return Value

Column name.

Example

Template Code	Expanded Code
/*If the current att. is renting-customer, then */ %AttFieldName	renting_customer


%AttFieldWidth

Returns the length of  the attribute datatype

%AttFieldWidth

Scope

%ForEachAtt, %ForEachFKAtt

Return Value

Numeric

Example

Template Code	Expanded Code
/* For a datatype of varchar(50)*/ %AttFieldWidth	50


%AttId

Returns the attribute ID.

%AttId

Scope

%ForEachAtt, %ForEachFKAtt

Return Value

Integer


%AttIsFK

A Boolean predicate that lets you determine whether or not the current attribute is a foreign key member.

%AttIsFK

Scope

%ForEachAtt, %ForEachFKAtt, %If

Return Value

TRUE/FALSE

Example

Template Code	Expanded Code
%ForEachAtt() {     %If{        %AttIsFK}     %AttName}	master-number movie-copy-number


%AttIsRolenamed

A Boolean predicate that lets you determine whether or not the current attribute  is rolenamed. See %AttIsFK for expansion information.

%AttIsRolenamed

Scope

%ForEachAtt, %ForEachFKAtt, %If

Return Value

TRUE/FALSE


%AttIsPK

A Boolean predicate that lets you determine whether or not the current attribute  is a primary key member. See %AttIsFK for expansion information.

%AttIsPK

Scope

%ForEachAtt, %ForEachFKAtt, %If

Return Value

TRUE/FALSE


%AttName

Returns the logical name of the current attribute.

%AttName

Scope

%ForEachAtt, %ForEachFKAtt

Return Value

Attribute name.

Example

Template Code	Expanded Code
/*If the current att. is master-number, then */ %AttName	master-number


%AttNullOption

Returns the null option of the current attribute.

%AttNullOption

Scope

%ForEachAtt, %ForEachFKAtt

Return Value

NULL/NOT NULL


%AttPhysDatatype

Returns the physical datatype of the current attribute, even for user-defined datatypes.

%AttPhysDatatype

Scope

%ForEachAtt, %ForEachFKAtt

Return Value

Datatype

Example

Template Code	Expanded Code
/*If the current att. is master-number, then */ %AttPhysDatatype	int


%Atts

Lists all the attributes of the trigger entity, performing the specified function on each member.

%Atts(<separator>,<action>, <prefix>)

Scope

Trigger Override

Return Value

List of actions and attributes.

Example

Template Code	Expanded Code
%Atts(",  ",update,%Parent)	update(MOVIE_COPY.master_number), update(MOVIE_COPY.movie_copy_number)


%AttValidation

Returns the name of the validation attached to a given attribute.

%AttValidation

Scope

%ForEachAtt, %ForEachFKAtt

Return Value

Validation Name

Example

Template Code	Expanded Code
/*If the current att. is due_date, then */ %AttValidation	DUE_DATE_RULE


%Cardinality

Returns relationship cardinality:

♦	<nothing> = 1:0,1, or more

♦	P = 1:1 or more

♦	Z = 1:0 or 1

♦	n = 1:n, where n is an integer.

%Cardinality

Scope

RI or Rel Override

Return Value

<nothing>, P, Z, <any integer>


%Child

Returns the physical table name of the child entity in a relationship.

%Child

Scope

RI or Rel Override

Return Value

Table name.

Example

Template Code	Expanded Code
/*If the current relationship is <is in stock as>, then */ %Child	MOVIE_RENTAL_RECOR


%ChildAtts

Lists all the attributes of the child entity in a relationship, and performs the  specified action on each.

%ChildAtts(<separator>,<action>, <prefix>)

Scope

RI or Rel Override

Return Value

Attribute names and actions.

Example

Template Code	Expanded Code
/*If the current relationship is <is in stock as>, then */ %ChildAtts(" or ",update)	update(movie_copy_number) or update(master_number) or ...


%ChildFK

Lists the foreign keys of the child entity in a relationship and performs the specified action on each.

%ChildFK(<separator>,<action> <prefix>)

Scope

RI or Rel Override

Return Value

Attribute names and actions.

Example

Template Code	Expanded Code
/*If the current relationship is <is in stock as>, then */ %ChildFK(" or ",update)	update(master_number) or update(movie_copy_number)


%ChildFKDecl

Lists the foreign keys of the child entity in the relationship followed by their  datatypes.

%ChildFKDecl(<old prefix>, <new prefix>,<separator>>,<attribute/type separator>)

Scope

RI or Rel Override

Return Value

FK attribute names and datatypes

Example

Template Code	Expanded Code
/*If the current relationship is <is in stock as>, then */ %ChildFKDecl(,@ins_,", ")	@ins_master_number int, @ins_movie_copy_number int


%ChildNK

Lists non-key elements of the child entity and performs the specified action on each.

%ChildNK(<separator>,<action>, <prefix>)

Scope

RI or Rel Override

Return Value

Non-key attribute names and action

Example

Template Code	Expanded Code
/*If the current relationship is <is in stock as>, then */ %ChildNK(" or ",update,@)	update(@general_condition)


%ChildNKDecl

Lists the non-key elements of the child entity in the relationship followed by their datatypes.

%ChildNKDecl(<old prefix>, <new prefix>,<separator>>,<attribute/type separator>)

Scope

RI or Rel Override

Return Value

Non-key attribute names and datatypes

Example

Template Code	Expanded Code
/*If the current relationship is <is in stock as>, then */ %ChildNKDecl(,@ins_,", ")	@ins_general_condition varchar(10)


%ChildParamDecl

Lists all the attributes of the child entity in the relationship followed by their datatypes. This is useful when declaring parameters in stored procedures.

%ChildParamDecl(<old prefix>, <new prefix>, <separator>>,<attribute/type separator>)

Scope

RI or Rel Override

Return Value

Attribute names and datatypes

Example

Template Code	Expanded Code
/*If the current relationship is <is in stock as>, then */ %ChildParamDecl(,@ins_,", ")	@ins_master_number int, @ins_movie_copy_number int, @ins_general_condition varchar(10),


%ChildPK

Lists primary key elements of the child entity, and performs the specified action on each.

%ChildPK(<separator>,<action>, <prefix>)

Scope

RI or Rel Override

Return Value

Non-key attribute names and actions

Example

Template Code	Expanded Code
/*If the current relationship is <is in stock as>, then */ %ChildPK(" or ",update,@)	update(@movie_copy_number) or update(@master_number)


%ChildPKDecl

Lists the primary key elements of the child entity in the relationship followed by their  datatypes.

%ChildPKDecl(<old prefix>, <new prefix>,<separator>>,<attribute/type separator>)

Scope

RI or Rel Override

Return Value

Attribute names and datatypes

Example

Template Code	Expanded Code
/*If the current relationship is <is in stock as>, then */ %ChildPKDecl(,@ins_,", ")	@ins_master_number int, @ins_movie_copy_number int,


%Concat

Concatenates <value1> and <value2> and returns the result.

%Concat(<value1>,<value2>)

Scope

Global

Return Value

String


%CurrentDatabase

Returns the name of the database in use when the script was generated, as entered in the <DB> Connection dialog.

%CurrentDatabase

Scope

Global

Return Value

String

Example

Template Code	Expanded Code
%CurrentDatabase	testdb


%CurrentFile

Returns the name of the ERwin file from which the script was generated.

%CurrentFile

Scope

Global

Return Value

String

Example

Template Code	Expanded Code
%CurrentFile	C:\MYFILES\MOVIES.ER1


%CurrentServer

Returns the name of the target server in use when the script was generated, as entered in the <DB> Connection dialog.

%CurrentServer

Scope

Global

Return Value

String

Example

Template Code	Expanded Code
%CurrentServer	SQL6


%CurrentUser

Returns the name of the user who generated the script, as entered in the <DB> Connection dialog.

%CurrentUser

Scope

Global

Return Value

String

Example

Template Code	Expanded Code
%CurrentUser	ssmith


%CustomTriggerDefaultBody

Inserts the code from the trigger body template into the trigger in expanded form.

%CustomTriggerDefaultBody

Scope

Trigger Override

Return Value

All code contained in the CustomTriggerDefaultBody trigger template.


%CustomTriggerDefaultFooter

Inserts the code from the trigger footer template into the trigger in expanded form.

%CustomTriggerDefaultFooter

Scope

Trigger Override

Return Value

All code contained in the CustomTriggerDefaultFooter trigger template.


%CustomTriggerDefaultHeader

Inserts the code from the trigger header template into the trigger in expanded form.

%CustomTriggerDefaultHeader

Scope

Trigger Override

Return Value

All code contained in the CustomTriggerDefaultHeader trigger template.


%DatatypeName

Returns the name of the datatype.

%DatatypeName()

Scope

Global

Return Value

Datatype

Example

Template Code	Expanded Code
%ForEachAtt(%Tablename,", "){  %DatatypeName(%AttPhysDatatype)}	varchar


%DatatypeScale

For decimal datatypes, %DatatypeScale returns the number of decimal places.

%DatatypeScale()

Scope

Global

Return Value

Integer

Example

Template Code	Expanded Code
/* For example, in decimal(10,2)*/ %DatatypeScale()	2


%DatatypeWidth

Returns the field width of the datatype.

%DatatypeWidth()

Scope

Global

Return Value

Integer

Example

Template Code	Expanded Code
/* For example, in decimal(10,2)*/ %DatatypeWidth()	10


%Datetime

Returns the current date and time.

%Datetime

Scope

Global

Return Value

String

Example

Template Code	Expanded Code
%Datetime	Wed Feb 17 12:56:22 1998


%DBMS

Returns the database name.

%DBMS

Scope

Global

Return Value

String

Example

Template Code	Expanded Code
%DBMS	ORACLE


%DBMSDelim

Returns the statement delimiter of the DBMS.

%DBMSDelim

Scope

Global

Return Value

, or ; or other delimiter


%Decl

Declares a <variable>and, optionally, initializes it with <initial value>. For example, if you include the %Decl(var1,0) statement, on expansion, var1 is declared and initialized with the value 0. No expansion code is generated.

%Decl(<variable>,<initial value>)

Scope

Global

Return Value

None

Example

Template Code	Expanded Code
%Decl(var1,0)	Internally, var1 is set to the value of 0.


%DefaultName

Returns the default's name.

%DefaultName

Scope

%ForEachDefault

Return Value

Name of the default.

Example

Template Code	Expanded Code
%ForEachDefault(", "){ %DefaultName}	SPACES, PERSON-NAME, ADDRESS


%DefaultValue;DefaultValue;
%DefaultValue

Returns the default's value.

%DefaultValue(<default name>) or %DefaultValue

Scope

Global with an argument (<default name>) or %ForEachDefault without an argument

Return Value

Value of the default.

Example

Template Code	Expanded Code
%ForEachDefault(", "){ %DefaultValue}	NEW RELEASE, COMEDY, DRAMA, CHILDREN, HORROR


%DomainDatatype;DomainDatatype;
%DomainDatatype

Returns the physical datatype of the domain.

%DomainDatatype(<domain name>)or %DomainDatatype

Scope

Global with an argument (<domain name>) or %ForEachDomain

Return Value

Datatype

Example

Template Code	Expanded Code
%ForEachDomain(", "){ %DomainDatatype}	varchar(10), int, char


%DomainDef

Returns the name of the default attached to the domain.

%DomainDef(<domain name>) or %DomainDef

Scope

Global with an argument (<domain name>) or %ForEachDomain

Return Value

Domain’s default value.

Example

Template Code	Expanded Code
%ForEachDomain(", "){ %DomainDef}	NEW RELEASE


%DomainName

Returns the name of the domain.

%DomainName

Scope

%ForEachDomain

Return Value

Domain name.

Example

Template Code	Expanded Code
%ForEachDomain{ %DomainName}	PERSON NAME ADDRESS


%DomainNullOption;DomainNullOption;Null option;
%DomainNullOption

Returns the null option of the domain.

%DomainNullOption(<domain name>) or %DomainNullOption

Scope

Global with an argument (<domain name>) or %ForEachDomain

Return Value

NULL, NOT NULL, IDENTITY, WITH NULL, or NOT NULL WITH DEFAULT


%DomainValidation

Returns the name of a validation attached to the domain.

%DomainValidation(<domain name>) or %DomainValidation

Scope

Global with an argument (<domain name>) or %ForEachDomain

Return Value

Domain name.

Example

Template Code	Expanded Code
%ForEachDomain{ %DomainValidation}	IS VALID CUSTOMER, DUE DATE


%EntityId

Returns the ID of the entity or table.

%EntityId(<entity or tablename>)

Scope

RI or Rel OverrideTrigger Override

Return Value

Integer

Example

Template Code	Expanded Code
%EntityId(%Parent)	1234


%EntityName

Returns the name of the entity or table.

%EntityName(<entity or tablename>)

Scope

RI or Rel OverrideTrigger Override

Return Value

String

Example

Template Code	Expanded Code
%EntityName(%Parent)	MOVIE-COPY


%File

Expands the macro code within the braces and writes the result to the given file. This macro provides a workaround to the Windows 3.x 64K string size limit.

%File(<filename>) {<macro code>}

Scope

Global

Return Value

String

Example

Template Code	Expanded Code
%File(C:\erw\myfile.tst) {%ForEachAtt(Parent, ", ") {%AttFieldName} ...}	<creates a file and includes the specified information>


%Fire

Specifies when the trigger is fired. Available on INFORMIX, ORACLE7, and Rdb only.

%Fire

Scope

Trigger Override

Return Value

BEFORE or  AFTER

Example

Template Code	Expanded Code
%Fire	BEFORE


%ForEachAtt

Expands macro code for each of the attributes in the given table.

%ForEachAtt(<table name>,<separator>) { <macro code> }

Scope

Trigger Override

Return Value

Result of <macro code> for each attribute in a given table.

Example

Template Code	Expanded Code
%ForEachAtt(%Parent, ", ") {%AttFieldName}	master_number, movie_copy_number, general_condition


%ForEachChildRel;ForEachChildRel;
%ForEachChildRel

Expands the <relationship code> for every relationship in which the trigger entity is a child.

%ForEachChildRel(<separator>) {<relationship code>}

Scope

Trigger Override

Return Value

Result of <macro code> for each relationship.

Example

Template Code	Expanded Code
%ForEachChildRel("go") {   insert %Parent values (%ParentAtts(", ",,@ins_) }	insert MOVIE_COPY values(@ins_master_number, @ins_movie_copy_number, @ins_general_condition) go


%ForEachDefault

Expands the macro code for each default.

%ForEachDefault(<separator>) {<macro code>}

Scope

Global

Return Value

Result of <macro code> for each default.

Example

Template Code	Expanded Code
%ForEachDefault( ){%DefaultName }	FILL_WITH_SPACES SET_TO_NULL SET_TO_ZERO


%ForEachDomain

Expands the macro code for all domains that are implemented as user-defined datatypes.

%ForEachDomain(<separator>) {<macro code>}

Scope

Global

Return Value

Result of <macro code>

Example

Template Code	Expanded Code
%ForEachDomain(","){%DomainName }	ADDRESS,  PERSON-NAME


%ForEachEntity

Expands the macro code for all entities in the current subject area.

%ForEachEntity(<separator>) {<macro code>}

Scope

Trigger Override

Return Value

Result of <macro code> for each entity.

Example

Template Code	Expanded Code
%ForEachEntity() {%TableName}	MOVIE_COPY MOVIE_RENTAL_RECOR


%ForEachFKAtt

Expands macro code for each of the foreign key attributes migrated through the current relationship.

%ForEachFKAtt(<separator>) {<macro code>}

Scope

RI or Rel Override

Return Value

Result of <macro code> for each FK attribute.

Example

Template Code	Expanded Code
%ForEachFKAtt(", ") {%AttFieldName}	master_number, movie_copy_number


%ForEachIndex

Expands the macro code for all indexes in the current subject area. <table> defaults to the table in the current scope; can be used to name another table (%Parent, MOVIE_COPY).<type>filters type of index (AK, IE, IF, PK, AK1, IE2, etc.). Defaults to all.

%ForEachIndex([<table>],[<type>],[<name>],[<separator>]) {<macro code>}

Scope

Trigger Override

Return Value

Result of <macro code> for each index.

Example

Template Code	Expanded Code
%ForEachIndex {  %IndexName %IndexType}	XPKMOVIE_COPY PK


%ForEachIndexMem

Expands the macro code for all members of the index in the current subject area.  <sequence> can be used to enter the assigned number of a specific index member.  Defaults to all members.

%ForEachIndexMem([<sequence>],[<separator>]) {<macro code>}

Scope

Trigger Override

Return Value

Result of <macro code> for each index member.

Example

Template Code	Expanded Code
%ForEachIndex {   %ForEachIndexMem() {       %AttDatatype }}	int int varchar(10)


%ForEachKey

Expands the macro code for all alternate keys and inversion entries in the current subject area.

%ForEachKey([<table>],[<type>],[<separator>]) {<macro code>}

Scope

Trigger Override

Return Value

Result of <macro code> for each key.

Example

Template Code	Expanded Code
%ForEachKey(%Parent,,, ) {%KeyName}	AK1, IE1


%ForEachKeyMem

Expands the macro code for all members of the key.

%ForEachKeyMem([<sequence>],[<separator>]) {<macro code>}

Scope

Trigger Override

Return Value

Result of <macro code> for each key member.

Example

Template Code	Expanded Code
%ForEachKey {%ForEachKeyMem() {%AttName}}	renting-customer


%ForEachParentRel

Expands the <relationship code> for every relationship in which the trigger entity is a parent.

%ForEachParentRel(<separator>) { <relationship code> }

Scope

Trigger Override

Return Value

Result of <macro code> for each relationship.

Example

Template Code	Expanded Code
%ForEachParentRel() {insert %Parent values(%ParentAtts(", ",,@ins_) }	insert MOVIE_COPY values(@ins_master_number, @ins_movie_copy_number, @ins_general_condition)


%ForEachValidValue

Expands the macro code for all valid values within the validation rule.

%ForEachValidValue(<separator>) {<macro code>}

Scope

Trigger Override, %ForEachValidation

Return Value

Result of <macro code> for each valid value.

Example

Template Code	Expanded Code
%ForEachValidValue(","){   %ValidValue =>%ValidValueDef }	N =>New (recent release), S =>Standard release, O =>Old movie, C =>Classic


%ForEachValidation

Expands the macro code for all validation rules.

%ForEachValidation(<separator>) {<macro code>}

Scope

Trigger Override

Return Value

Result of <macro code> for each validation rule.

Example

Template Code	Expanded Code
%ForEachValidation(){    %ValidationName }	Due Date Validation


%If  %Else

Conditionally expands either the %If<macro code1> if the predicate evaluates to "true" or the %Else <macro code2> if the predicate evaluates to "false". The %Else clause is optional.

%If (<predicate>) {<macro code1>} %Else {<macro code2>}

Scope

Global

Return Value

Result of <macro code1> each time <predicate> evaluates to TRUE.
Result of <macro code2> each time <predicate> evaluates to FALSE.

Example

Template Code	Expanded Code
%ForEachAtt() {   %If(%AttIsPK) {        %AttName -> PK}}	master-number -> PK movie-copy-number -> PK


%Include

Allows you to include trigger macro code across multiple files or trigger templates.

%Include("<path name>")

%Include(<trigger template name>)

Scope

Global

Return Value

String

Example

Template Code	Expanded Code
%Include("<c:\er\trg.tdl")	<All code included in the trg.tdl file>


%IndexName

Returns the index name.

%IndexName

Scope

Trigger Override, %ForEachIndex, %ForEachIndexMem

Return Value

String

Example

Template Code	Expanded Code
%IndexName	XPK_MOVIE_COPY


%IndexType

Returns the index type.

%IndexType

Scope

Trigger Override, %ForEachIndex, %ForEachIndexMem

Return Value

FK, PK, AK, IE1, etc.

Example

Template Code	Expanded Code
%IndexType	PK


%JoinFKPK

Lets you join the foreign key of a child entity to the primary key of the parent entity in a relationship.

%JoinFKPK([<child table>, <parent table>,<comparison op>,<separator>)

Scope

RI or Rel Override, WHERE clause search condition

Return Value

Code supporting comparison of FK and PK values in a relationship.

Example

Template Code	Expanded Code
%JoinFKPK(%Child, deleted," <> "," or")	MOVIE_RENTAL_RECOR.master_number  <> deleted.master_number or MOVIE_RENTAL_RECOR.movie_copy_number <> deleted.movie_copy_number


%JoinPKPK

Lets you join the primary key of two correlations, or a base table and a correlation.

%JoinPKPK(<table>, <correlation>, <comparison op>,<separator>)

Scope

RI or Rel OverrideTrigger Override, WHERE clause search condition

Return Value

Code supporting comparison of PK values of two tables.

Example

Template Code	Expanded Code
%JoinPKPK(%Child,ins," <> "," or")	MOVIE_RENTAL_RECOR.renting_customer <> ins.renting_customer or MOVIE_RENTAL_RECOR.master_number <> ins.master_number or …


%KeyName

Returns the key name.

%KeyName

Scope

Trigger Override, %ForEachKey, %ForEachKeyMem

Return Value

String

Example

Template Code	Expanded Code
%KeyName	AK1


%Len

Returns the string length of <macro code>.

%Len(<macro code>)

Scope

Global

Return Value

Integer

Example

Template Code	Expanded Code
string1=’aaaaa’ %Len(%:string1)	5


%Lower

Converts the expansion of <macro code> to lowercase.

%Lower(<macro code>)

Scope

Global

Return Value

Lowercase string

Example

Template Code	Expanded Code
%Lower(%Parent)	movie


%Max

Compares <value1> and <value2> and returns the maximum.

%Max(<value1>,<value2>)

Scope

Global

Return Value

Larger value in comparison

Example

Template Code	Expanded Code
var1=8 var2=5 %Max(var1,var2)	8


%Min

Compares <value1> and <value2> and returns the minimum.

%Min(<value1>,<value2>)

Scope

Global

Return Value

Smaller value in comparison

Example

Code	Expansion
var1=8 var2=5 %Min(var1, var2)	5


%NK

Lists all the non-key attributes of the trigger entity, and performs the specified action on each.

%NK(<separator>,<action>, <prefix>)

Scope

Trigger Override

Return Value

Attribute and action

Example

Template Code	Expanded Code
%NK(" or ",update,%Parent)	update(MOVIE_COPY.general_condition) or...


%NKDecl

Lists the non-key attributes of the trigger entity followed by their datatypes.

%NKDecl(<old prefix>,<new prefix>,<separator>,<attribute/type separator>)

Scope

Trigger Override

Return Value

Attribute names and datatypes

Example

Template Code	Expanded Code
%NKDecl(,@ins_,", ")	@ins_general_condition varchar(10)


%Not

Lets you combine Boolean expressions to create more complex comparisons. %Not performs a "logical not" on the Boolean predicate defined by <macro code>.

%Not(<macro code>)

Scope

Global, %If

Return Value

Result of macro code.

Example

Template Code	Expanded Code
%ForEachAtt() {    %If(%Not(%==(%AttDatatype, int))) {%AttName}}	general-condition


%NotnullFK

Compares the foreign key of a child entity <child table>in a relationship to <null expression>. This macro expands if and only if the relationship is non-identifying , nulls allowed.

%NotnullFK(<child table>, <not null expression>,<prefix>,<separator>)

Scope

RI or Rel Override, WHERE clause search condition

Return Value

Null expression for each FK allowing null values.

Example

Template Code	Expanded Code
where %NotnullFK(,"is null",," or ")	where master_number is null or  movie_copy_number is null


%Or

Lets you combine Boolean expressions to create more complex comparisons. %Or performs a "logical or" on Boolean predicates defined by <macro code1> and <macro code2>.  For example, %If {%Or(%AttIsFK,%AttIsRolenamed)…} lets you execute additional steps if the given attribute is either a foreign key or is rolenamed. The macro is executed if any element evaluates to "TRUE".

%Or(<macro code1>,<macro code2>)

Scope

Global, %If

Return Value

TRUE/FALSE

Example

Template Code	Expanded Code
%If (%Or(%AttIsFK, %AttIsRolenamed){ %AttName}	master_copy_number


%ParamDecl

Lists all attributes of the trigger entity followed by their datatypes.

%ParamDecl(<old prefix>,<new prefix>,<separator>,<attribute/type separator>)

Scope

Trigger Override

Return Value

Attributes and datatypes

Example

Template Code	Expanded Code
%ParamDecl(, @ins_,", ")	@ins_master_number int, @ins_movie_copy_number int,  @ins_general_condition varchar(10)


%ParamPass

Assigns values to procedure parameters specified by the <old prefix> and/or the <new prefix>, for all attributes of the trigger entity. Available on Ingres only.

%ParamPass(<old prefix>,<new prefix>,<param/value separator>,<param separator>)

Scope

Trigger Override

Return Value

Parameter list for Ingres

Example

Template Code	Expanded Code
%ParamPass(,new," = ")	master_number = new.master_number


%Parent

Returns the physical table name of the parent entity in a relationship.

%Parent

Scope

RI or Rel Override

Return Value

String

Example

Template Code	Expanded Code
%Parent	MOVIE_COPY


%ParentAtt

Expands any attribute macro (e.g., %AttFieldName, %AttDatatype) on the parent primary key attribute from which the current attribute is migrated.

%ParentAtt(<attribute macro>)

Scope

%ForEachAtt, %ForEachFKAtt

Return Value

Result of <attribute macro>

Example

Template Code	Expanded Code
%ForEachAtt() {%ParentAtt (%AttFieldName)}	master_number


%ParentAtts

Lists all the attributes of the parent entity in a relationship, and performs the specified action on each.

%ParentAtts(<separator>, <action>,<prefix>)

Scope

RI or Rel Override

Return Value

Attributes and actions

Example

Template Code	Expanded Code
%ParentAtts(" or ",update,%Parent)	update(MOVIE.master_number) or  update(MOVIE.customer_number)…


%ParentNK

Lists the non-key attributes of the parent entity in a relationship, and performs the specified action on each.

%ParentNK(<separator>, <action>,<prefix>)

Scope

RI or Rel Override

Return Value

Non-key attributes and actions

Example

Template Code	Expanded Code
%ParentNK(" or ",update,%Parent)	update(MOVIE.movie_name) or update(MOVIE.movie_rating)or update(MOVIE.movie_year)


%ParentNKDecl

Lists the non-key attributes of the parent entity in the relationship followed by their datatypes.

%ParentNKDecl(<old prefix>, <new prefix>,<separator>,<attribute/type separator>)

Scope

RI or Rel Override

Return Value

FK attribute names and datatypes

Example

Template Code	Expanded Code
/*If the current relationship is <is in stock as>, then */ %ParentNKDecl(,@ins_,", ")	@ins_movie_name varchar(50), @ins_movie_rating varchar(5), @ins_movie_rental_rate money


%ParentParamDecl

Lists the attributes of the parent entity in the relationship followed by their datatypes.

%ParentParamDecl(<old prefix>, <new prefix>, <separator>,<attribute/type separator>)

Scope

RI or Rel Override

Return Value

Attribute names and datatypes

Example

Template Code	Expanded Code
/*If the current relationship is <is in stock as>, then */ %ParentParamDecl(, @ins_,", ")	@ins_movie_name varchar(50), @ins_movie_rating varchar(5), @ins_movie_rental_rate money


%ParentPK

Lists the primary key elements of the parent entity in a relationship, and performs the specified action on each.

%ParentPK(<separator>,<action>)

Scope

RI or Rel Override

Return Value

Primary key attributes and actions

Example

Template Code	Expanded Code
%ParentPK(,update,%Parent)	update(MOVIE_COPY.master_number) update(MOVIE_COPY.movie_copy_number)


%ParentPKDecl

Lists the primary key elements of the parent entity in the relationship followed by their datatypes.

%ParentPKDecl(<old prefix>, <new prefix>,<separator>,<attribute/type separator>)

Scope

RI or Rel Override

Return Value

Primary key attribute names and datatypes

Example

Template Code	Expanded Code
/*If the current relationship is <is in stock as>, then */ %ParentPKDecl(,@ins_)	@ins_master_number varchar(10) @ins_movie_copy_number varchar(10) @ins_customer_number varchar(10)


%PhysRelName

Returns the physical relationship name.

%PhysRelName

Scope

RI or Rel Override

Return Value

String

Example

Code	Expansion
%PhysRelName	is_rented_under


%PK

Lists the primary key elements of the trigger entity, and performs the specified action on each.

%PK(<separator>,<action>,<prefix>)

Scope

Trigger Override

Return Value

Primary key attributes and actions

Example

Template Code	Expanded Code
%PK(,update,%Parent)	update(MOVIE_COPY.master_number) update(MOVIE_COPY.movie_copy_number)


%PKDecl

Lists the primary key elements of the trigger entity followed by their datatypes.

%PKDecl(<old prefix>,<new prefix>,<separator>>,<attribute/type separator>))

Scope

Trigger Override

Return Value

Primary key attribute names and datatypes

Example

Template Code	Expanded Code
%PKDecl(,@ins_)	@ins_master_number varchar(10) @ins_movie_copy_number varchar(10)


%RefClause

Generates the references clause from the OLD and NEW information added in the Entity Trigger Editor. Available on INFORMIX, Ingres, ORACLE7, and Rdb only.

%RefClause

Scope

Trigger Override

Return Value

Reference clause

Example

Template Code	Expanded Code
%RefClause	REFERENCES NEW as inserted OLD as deleted


%RelId

Returns the relationship ID.

%RelId

Scope

RI or Rel Override

Return Value

Integer


%RelIsNonull

Examines the relationship null expression and returns "true" if it is nulls are not allowed and "false" if nulls are allowed.

%RelIsNonull

Scope

RI or Rel Override, %If

Return Value

TRUE/FALSE


%RelRI

Returns the given referential integrity option for the specified <action> (insert, delete, update) and <RI type> (parent or child).

%RelRI(<action>,<RI type>)

Scope

RI or Rel Override

Return Value

CASCADE, RESTRICT, SET NULL, SET DEFAULT, or NONE

Example

Template Code	Expanded Code
%RelRI(Update, Child)	CASCADE


%RelTemplate

Expands the template code attached to the current relationship. If no code is attached, the corresponding diagram-wide referential integrity template is expanded.

%RelTemplate

Scope

RI or Rel Override

Return Value

Expands the code fragment for the relationship corresponding to trigger type.


%RelType

Returns the relationship type. Relationship type valid values and their definitions include: RT_ID (identifying), RT_NI (non-identifying), RT_SC (subtype), or RT_MM (many-to-many).

%RelType

Scope

RI or Rel Override

Return Value

RT_ID, RT_NI, RT_SC, or RT_MM

Example

Template Code	Expanded Code
%RelType	RT_ID


%Scope

Returns the specification of how the trigger is to be executed. Available on ORACLE7 only.

%Scope

Scope

Trigger Override

Return Value

FOR EACH ROW or FOR EACH TABLE

Example

Template Code	Expanded Code
%Scope	FOR EACH ROW


%SetFK

Lists the foreign key elements of a child entity in a relationship and sets each member to the specified value.

%SetFK(<child table>,<value>)

Scope

RI or Rel Override

Return Value

Foreign key attribute name and value

Example

Template Code	Expanded Code
%SetFK(%Child,NULL)	MOVIE_RENTAL_RECOR.master_number = NULL MOVIE_RENTAL_RECOR.movie_copy_number = NULL MOVIE_RENTAL_RECOR.customer_number = NULL MOVIE_RENTAL_RECOR.rental_record_id = NULL


%SetPK

Lists the primary key elements of the specified table and sets each member to the specified value.

%SetPK(<table>,<value>)

Scope

RI or Rel OverrideTrigger Override

Return Value

Primary key attribute name and value

Example

Template Code	Expanded Code
%SetPK(%Parent,0)	MOVIE_COPY.master_number = 0, MOVIE_COPY.movie_copy_number = 0


%Substitute

Substitutes string <pattern> in string <value> with  string <substitute>.

%Substitute(<value>,<pattern>,<substitute>)

Scope

Global

Return Value

String

Example

Template Code	Expanded Code
/*If the name of the child table is movie_copy_number*/ %Substitute(%Child, movie,ins_movie)	ins_movie_copy_number


%Substr

Generates a substring of the expansion of the given <macro code>. <length> is not required.

%Substr(<macro code>,<initial pos>,<length>)

Scope

Global

Return Value

String

Example

Template Code	Expanded Code
%Substr(macro,1,3)	mac


%Switch

Evaluates the argument against the specified choices. ERwin expands the macro code corresponding to the matching choice. If no match is found, ERwin expands the default macro code.

%Switch(<argument>) {%Choose(<choice 1>) {<macro code 1>}
    %Choose(<choice 2>) {<macro code 2>}   <etc...> %Default {<default macro code>}}

Scope

Global

Return Value

Macro code value

Example

Template Code	Expanded Code
/* If %TableName is MOVIE_COPY */ %Switch(%TableName){ %Choose(MOVIE_COPY){    %FK} %Choose(MOVIE_RENTAL_RECOR){    %PK} %Default{%PK}}	master_number


%TableName

Returns the physical table name of the trigger entity.

%TableName

Scope

Trigger Override

Return Value

Name of table

Example

Template Code	Expanded Code
%Tablename	MOVIE_COPY


%TemplateName

Returns the name of the trigger, stored procedure, or script template.

%TemplateName

Scope

Trigger Override

Return Value

Template name

Example

Template Code	Expanded Code
%TemplateName	CHILD_DELETE_CASCADE


%TriggerName

Returns the physical trigger name.

%TriggerName

Scope

Trigger Override

Return Value

Trigger name

Example

Template Code	Expanded Code
%TriggerName	MOVIE_COPY_CHILD_DELETE_CASCADE


%TriggerRelRI

Boolean predicate that is "true" if the given trigger and relationship are of the given <action>  (Update/Delete/Insert), <RI type> (Child/Parent), and <integrity > (Cascade/Restrict/Set Null/Set Default).

%TriggerRelRI(<action>, <RI type>, <integrity>)

Scope

RI or Rel Override, %If

Return Value

TRUE/FALSE

Example

Template Code	Expanded Code
%ForEachChildRel() {  %If(%TriggerRelRI (Update, Child, Cascade)) {%VerbPhrase Child Update Cascade}}	is rented under Child Update Cascade


%UpdateChildFK

Lists the foreign key elements of the child entity in a relationship and performs the update action on each member. Available on ORACLE7, SQL Server, and SYBASE only.

%UpdateChildFK()

Scope

RI or Rel Override

Return Value

Foreign key value and action

Example

Template Code	Expanded Code
%UpdateChildFK()	update(master_number) or update(movie_copy_number) ...


%UpdateParentPK

Lists the primary key elements of the parent entity in a relationship and performs the update action on each member. Available on ORACLE7, SQL Server, and SYBASE only.

%UpdateParentPK()

Scope

RI or Rel Override

Return Value

Primary key value and action

Example

Template Code	Expanded Code
%UpdateParentPK()	update(master_number)


%UpdatePK

Lists the primary key elements of the trigger entity and performs the update action on each member. Available on ORACLE7, SQL Server, and SYBASE only.

%UpdatePK()

Scope

Trigger Override

Return Value

Primary key value and action

Example

Template Code	Expanded Code
%UpdatePK()	update(master_number) update(movie_copy_number)


%Upper

Converts the expansion of <macro code> to uppercase.

%Upper(<macro code>)

Scope

Global

Return Value

String

Example

Template Code	Expanded Code
%Upper(%EntityName)	MOVIE COPY


%ValidationHasValidValues

A Boolean predicate that lets you determine whether or not the current validation has valid values.

%ValidationHasValidValues(<validation name>)

Scope

Global with an argument (<validation name>) or %ForEachValidation, %If

Return Value

TRUE/FALSE


%ValidationName

Returns the name of the validation.

%ValidationName

Scope

%ForEachValidation

Return Value

Validation Name

Example

Template Code	Expanded Code
%ValidationName	Due Date Validation


%ValidationRule

Returns the server-side validation rule.

%ValidationRule(<validation name>)or %ValidationRule

Scope

Global with an argument (<validation name>) or %ForEachValidation

Return Value

Validation rule string

Example

Template Code	Expanded Code
%ValidationRule (Valid CustNumber)	@col BETWEEN 1000 AND 9999


%ValidValue

Returns the data value of a valid value.

%ValidValue

Scope

%ForEachValidValue

Return Value

String

Example

Template Code	Expanded Code
%ValidValue	CHILDREN


%ValidValueDef

Returns the definition of a valid value.

%ValidValueDef

Scope

%ForEachValidValue

Return Value

String

Example

Template Code	Expanded Code
/* Where valid values are: COMEDY, HORROR, DRAMA, NEW RELEASE and CHILDREN, one definition could read: */ %ValidValueDef	A movie that is of interest to children.


%VerbPhrase

Returns the verb phrase of a relationship.

%VerbPhrase

Scope

RI or Rel Override

Return Value

Relationship verb phrase

Example

Template Code	Expanded Code
%VerbPhrase	is rented under